<?php
	class CategoryModel extends Model {
		private $queryGetRow = "";
		public function display( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("title_kh","title_en","description_kh","description_en") , $sortColumn="id,title_en" , $sortCriteria="ASC" ){					
			$start = is_numeric($start)&&$start>-1?$start:0;
			$perpage = is_numeric($perpage)&&$perpage>-1?$perpage:20;
				
			$result = array();
			$query = "SELECT id , 
						(SELECT m.name_en
							FROM az_menu as m
							WHERE c.`menu_id` = m.`id`) as menu,
						IFNULL( (SELECT c1.title_en 
							FROM az_categories as c1 
							WHERE c.`parent_id` = c1.`id`), './') as parent, 
						title_kh , title_en , published ,ordering,homepage, created , modified
					 FROM az_categories as c " ;
			if ( $searchString != "" ){
				$searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("title_en","title_en","description_kh","description_en") : $searchColumns ;
				if ( is_array($searchColumns) && !empty($searchColumns) ){
					$conditions = "" ;
					$conditions = array() ;
					foreach( $searchColumns AS $columnName ){
						$conditions[] = " $columnName LIKE '%$searchString%' " ;
					}
					$conditions = " WHERE " . implode("OR", $conditions );
					$sortColumn = $sortColumn===""||$sortColumn===false?"title_en":$sortColumn;
					$sortCriteria = $sortCriteria===""||$sortCriteria===false?"ASC":$sortCriteria;
					$query .= "$conditions " ;
				}
			}
			if( $sortColumn != "" || $sortColumn != false ){
				$sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"ASC";
				$query .= " ORDER BY $sortColumn $sortCriteria " ;
			}
			$this->queryGetRow = $query ;
			$query .= " LIMIT ".($start*$perpage)." , ".$perpage."; " ;
			//echo $query;
			$result = $this->executeQueryAsObject( $query ) ;
			return $result;
			/*
			 return $this->executeQueryAsObject("SELECT c.*,
			 		IFNULL( (SELECT c1.title_en
			 				FROM az_categories as c1
			 				WHERE c.`parent_id` = c1.`id`), './') as parent,
			 		(SELECT m.name_kh
			 				FROM az_menu as m
			 				WHERE c.`menu_id` = m.`id`) as menu
			 		FROM az_categories as c");
			*/
		}
		public function getTotalRecords(){
			return $this->getRowWithQuery($this->queryGetRow);
		}
		public function view($id = 0){
			return $this->executeQueryAsObject("SELECT * FROM az_categories WHERE `id` = '" . $id . "'");
		}
		public function edit($id = 0){
			return $this->executeQueryAsObject("SELECT * FROM az_categories WHERE `id` = '" . $id . "'");
		}
		public function update($data = array()){
			$fields = "";
			foreach($data as $table_name => $all_fields){
				$query = "UPDATE " . $table_name . " SET ";
				foreach($all_fields as $field_name => $value){
					$fields .= "`" . $field_name . "` = '" . $this->sqlRealEscape($value) . "',";
					//print_r($table_name);
				}
				$query .= $fields . " `modified` = NOW() WHERE `id` = " . $data[$table_name]['id'];
				//die($query);
				return $this->executeQuery($query);
			}
		}
		public function getMenuType(){
			return $this->executeQueryAsObject("
					SELECT 
						t.`id`,t.`menutype` 
					FROM 
						az_menu_types as t 
					INNER JOIN 
						az_menu as m ON t.`menutype` = m.`menutype` 
					GROUP BY 
						t.`menutype`
					ORDER BY 
						t.`menutype` ASC
					");
		}
		public function getMenu(){
			return $this->executeQueryAsObject("
					SELECT 
						m.`id`,m.`menutype`,m.`name_kh`,m.`name_en` 
					FROM 
						az_menu as m
					WHERE m.`published` = 1
					GROUP BY m.id
					ORDER BY 
						menutype,name_en,name_kh ASC");
		}
		public function getMenu2(){
			return $this->executeQueryAsObject("
					SELECT
					m.`id`,m.`menutype`,m.`name_kh`,m.`name_en`
					FROM
					az_menu as m INNER JOIN az_categories as c ON m.id = c.menu_id
					WHERE m.`published` = 1
					GROUP BY m.id
					ORDER BY
					menutype,name_en,name_kh ASC");
		}
		public function getParent(){
			return $this->executeQueryAsObject("
					SELECT 
						c.`id`,c.`title_kh`,c.`title_en`,c.`parent_id`,c.`menu_id` 
					FROM 
						az_categories as c
					ORDER BY 
						id,title_en, title_kh ASC");
		}
		public function add($data = array()){
			$fields = "";
			foreach($data as $table_name => $all_fields){
				$query = "INSERT INTO " . $table_name . " SET ";
				foreach($all_fields as $field_name => $value){
					$fields .= "`" . $field_name . "` = '" . $this->sqlRealEscape($value) . "',";
					//print_r($table_name);
				}
				$query .= $fields . " `ordering` = (SELECT MAX(c.`ordering`) + 1 FROM " . $table_name . " as c), `created` = NOW(), `modified` = NOW()";
				//die($query);
				return $this->executeQuery($query);
			}
		}
		public function delete($id = 0){
			$id = is_array($id)&&!empty($id)?implode(",",$id):$id;
			return $this->executeQuery("DELETE FROM az_categories WHERE `id` IN (" . $id . ")");
		}
		public function find( $id ){
			return $this->executeQueryAsObject("SELECT * FROM az_categories WHERE id = '$id' ;");
		}
		public function getCategory($menu_id = 0){
			$query = "SELECT `id`,`title_en` as title FROM az_categories WHERE `menu_id` = '$menu_id'";//die($query);
			return $this->executeQueryAsObject($query);
		}
		public function getCategoryByCateID($cate_id = 0){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
					SELECT `id` as cate_id ,`title_en` as cate_title,`description_en` as cate_description
				";
			}else{
				$query = "
					SELECT `id` as cate_id ,`title_kh` as cate_title, `description_kh` as cate_description
					";
			}
			$query .= "
			FROM az_categories as c
			WHERE
			c.id = '$cate_id' AND
			c.published = 1";
			return $this->executeQueryAsObject($query);
		}
		public function getArticle($cate_id = 0,$limit = "30",$page=1){
			$lang = $_SESSION['lang'];
            $query = "";
            $page = isset($_SESSION['nav'])?$_SESSION['nav']['page-article-by-menu']:$page;
            $page = $page<1?1:$page;
			if($lang == "en"){
				$query = "
				SELECT
				m.id as menu_id,
				a.id as article_id,a.catid as cate_id,a.title_en as article_title,a.introtext_en as article_introtext,a.fulltext_en as article_fulltext ,a.created, a.is_thumbnail, a.image_thumbnail, c.`title_en` as cate_name,a.is_thumbnail, a.image_thumbnail,
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author,
				(SELECT `title_en` FROM `az_categories` WHERE `id` = '$cate_id') as cate_title,
				(SELECT `description_en` FROM `az_categories` WHERE `show_description` = 1 AND `id` = '$cate_id') as cate_description
				";
			}else{
				$query = "
				SELECT
				m.id as menu_id,
				a.id as article_id,a.catid as cate_id,a.title_kh as article_title,a.introtext_kh as article_introtext,a.fulltext_kh as article_fulltext,a.created, a.is_thumbnail, a.image_thumbnail, c.`title_kh` as cate_name,a.is_thumbnail, a.image_thumbnail,
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author,
				(SELECT `title_kh` FROM `az_categories` WHERE `id` = '$cate_id') as cate_title,
				(SELECT `description_kh` FROM `az_categories` WHERE `show_description` = 1 AND `id` = '$cate_id') as cate_description
				";
			}
			$query .= "
			FROM az_menu as m INNER JOIN az_categories as c ON m.id = c.menu_id ,
			az_articles as a
			WHERE
			c.id = '$cate_id' AND
			a.published = 1 AND `catid` = '$cate_id'
			ORDER BY a.`created`DESC ";
			$total = ceil($this->getRowWithQuery($query)/$limit);
			$query .= " LIMIT ".(($page-1)*$limit)." , ".$limit."; " ;
			return array("page"=>$page,"total"=>$total,"articles"=>$this->executeQueryAsObject($query));
		}
		public function getArticleByMenu($menu_id = 0, $limit = "5" , $page = 1 ){
			$lang = $_SESSION['lang'];
			$page = isset($_SESSION['nav'])?$_SESSION['nav']['page-article-by-menu']:$page;
			$page = $page<1?1:$page;
			if($lang == "en"){
				$query = "
						SELECT m.id as menu_id, a.`id` as article_id,a.`title_en` as article_title, a.`introtext_en` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail, c.`id` as cate_id, c.`title_en` as cate_name,
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
			}elseif($lang == "kh"){
				$query = "
						SELECT m.id as menu_id, a.`id` as article_id,a.`title_kh` as article_title, a.`introtext_kh` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail, c.`id` as cate_id, c.`title_kh` as cate_name,
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
			}
			$query .= "
			FROM `az_articles` as a RIGHT JOIN `az_categories` as c ON a.`catid` = c.`id` INNER JOIN `az_menu` as m ON m.`id` = c.`menu_id`
			WHERE c.`published` = 1 AND a.`published` = 1 AND c.`menu_id` = $menu_id
			ORDER BY a.`created` DESC";
            $total = ceil($this->getRowWithQuery($query)/$limit);
			$query .= " LIMIT ".(($page-1)*$limit)." , ".$limit."; " ;
			return array("page"=>$page,"total"=>$total,"articles"=>$this->executeQueryAsObject($query));
		}
	}
?>